[CHAPTER 05. IO와  Wait Event]

2. WAL File과 I/O
2-4. CASE STUDY


DROP TABLE IF EXISTS io_wal_test;
CREATE TABLE io_wal_test (
    a SERIAL PRIMARY KEY,
    b TEXT,
    c INTEGER,
    d TIMESTAMP DEFAULT NOW()
);

INSERT INTO io_wal_test (b, c)
SELECT   'test_data_' || s,
         s
FROM     generate_series(1, 10000000) AS s;

UPDATE   io_wal_test
SET      c = c + 1
WHERE    a % 2 = 0;

DELETE
FROM     io_wal_test
WHERE    a % 5 = 0;

SELECT   pid,
         event_type,
         event,
         queryid,
         count
FROM     pg_wait_sampling_profile
WHERE    event_type = 'IO'
AND      event LIKE 'WAL%'
ORDER BY event;

SELECT   *
FROM     pg_stat_wal;


3. Data File과 I/O
3-3. CASE STUDY


/* DataFileRead - default_statistics_target */
DROP TABLE IF EXISTS io_data_test;
CREATE TABLE io_data_test (
    a SERIAL PRIMARY KEY,
    b TEXT,
    c INTEGER,
    d TIMESTAMP DEFAULT NOW()
);

INSERT INTO io_data_test (b, c)
SELECT   'test_data_' || s,
         s
FROM     generate_series(1, 10000000) AS s;

UPDATE   io_data_test
SET      c = c + 1
WHERE    a % 2 = 0;

ANALYZE  io_data_test;

SELECT   pid,
         event_type,
         event,
         queryid,
         count
FROM     pg_wait_sampling_profile
WHERE    event_type = 'IO'
AND      event LIKE 'DataFile%'
ORDER BY event;

SELECT   *
FROM     pg_statio_user_tables
WHERE    relname = 'io_data_test';

/* DataFileWrite - bgwriter_delay */
DROP TABLE IF EXISTS io_data_test;
CREATE TABLE io_data_test (
    a SERIAL PRIMARY KEY,
    b TEXT,
    c INTEGER,
    d TIMESTAMP DEFAULT NOW()
);

INSERT INTO io_data_test (b, c)
SELECT   'test_data_' || s,
         s
FROM     generate_series(1, 20000000) AS s;

SELECT   pid,
         event_type,
         event,
         queryid,
         count
FROM     pg_wait_sampling_profile
WHERE    event_type = 'IO'
AND      event LIKE 'DataFile%'
ORDER BY event;

SELECT   *
FROM     pg_stat_bgwriter;


3-4. Checkpointer


/* checkpoint_timeout & max_wal_size */
DROP TABLE IF EXISTS io_checkpoint_test;
CREATE TABLE io_checkpoint_test (
    a SERIAL PRIMARY KEY,
    b TEXT,
    c INTEGER,
    d TIMESTAMP DEFAULT NOW()
);

INSERT INTO io_checkpoint_test (b, c)
SELECT   'test_data_' || s,
         s
FROM     generate_series(1, 20000000) AS s;

/* checkpoint_completion_target */
DROP TABLE IF EXISTS io_checkpoint_test;
CREATE TABLE io_checkpoint_test (
    a SERIAL PRIMARY KEY,
    b TEXT,
    c INTEGER,
    d TIMESTAMP DEFAULT NOW()
);

INSERT INTO io_checkpoint_test (b, c)
SELECT   'test_data_' || s,
         s
FROM     generate_series(1, 20000000) AS s;

SELECT   *
FROM     pg_stat_bgwriter;


4. Temp File 과 I/O
4-3. CASE STUDY


DROP TABLE IF EXISTS io_temp_test;
CREATE TABLE io_temp_test (
    a SERIAL PRIMARY KEY,
    b INTEGER,
    c DECIMAL(10, 2) NOT NULL,
    d DATE NOT NULL
);

INSERT INTO io_temp_test (b, c, d)
SELECT     (random() * 9999 + 1)::INT,
           (random() * 100)::DECIMAL(10, 2),
           CURRENT_DATE - (random() * 365)::INT
FROM       pg_catalog.generate_series(1, 10000000);

SELECT   *
FROM     io_temp_test
ORDER BY d DESC;

/* work_mem=1MB vs work_mem=4MB */
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, SUMMARY OFF)
SELECT   *
FROM     io_temp_test
ORDER BY d DESC;

SELECT   queryid,
         shared_blks_hit,
         shared_blks_read,
         temp_blks_read,
         temp_blks_written
FROM     pg_stat_statements;

SELECT   pid,
         event_type,
         event,
         queryid,
         count
FROM     pg_wait_sampling_profile
WHERE    event_type = 'IO'
AND      event LIKE 'BufFile%'
ORDER BY event;

/* work_mem=1MB + 인덱스 추가 */
CREATE INDEX idx_io_temp_test ON io_temp_test (d, c, b, a);

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, SUMMARY OFF)
SELECT   *
FROM     io_temp_test
ORDER BY d DESC;

SELECT   queryid,
         shared_blks_hit,
         shared_blks_read,
         temp_blks_read,
         temp_blks_written
FROM     pg_stat_statements;

SELECT   pid,
         event_type,
         event,
         queryid,
         count
FROM     pg_wait_sampling_profile
WHERE    event_type = 'IO'
AND      event LIKE 'BufFile%'
ORDER BY event;

/* BufFileWrite, BufFileRead - maintenance_work_mem */
VACUUM FULL io_temp_test;

SELECT   pid,
         event_type,
         event,
         queryid,
         count
FROM     pg_wait_sampling_profile
WHERE    event_type = 'IO'
AND      event LIKE 'BufFile%'
ORDER BY event;

SELECT   queryid,
         shared_blks_hit,
         shared_blks_read,
         temp_blks_read,
         temp_blks_written
FROM     pg_stat_statements;
